# -*- coding: utf-8 -*-
from datetime import datetime,timedelta
from win32com.client import Dispatch
import requests
import time
import StringIO
import os
import sys
# import json

oConn = Dispatch('ADODB.Connection')
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
oConn.Open()
def getexistctnno():        
    ctnno=[]
    # search_date=datetime.timedelta(days=-7)
    search_date=datetime.strftime(datetime.now()+timedelta(days=-7),"%Y-%m-%d")
    print search_date
    # oRS.Open("select xh from yb where xh<>'' and xdrq>=#"+search_date+"#")
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn    # Set the recordset to connect thru oConn
    oRS.Open("select id,xh from yb where xh<>'' and xh_info is null and rq>=#"+search_date+"#")
    while not oRS.EOF:
        # print oRS.Fields("cd_no").Value
        i={}
        i["id"]=oRS.Fields("id").Value
        i["xh"]=oRS.Fields("xh").Value
        ctnno.append(i)
        oRS.MoveNext()
    print u"查找到箱号：",ctnno
    oRS.Close()
    oRS=None
    return ctnno

def searchctnno(ctnno):
    # print ctnno["xh"]
    url='http://127.0.0.1:50003/query_npedi?query_id=1&ctn_no='+ctnno["xh"]
    r=requests.get(url).json()
    # print search_str
    
    if len(r['result'])<=1:
        print 'not find'
        return
    else:
        print r['result'][1]
        xh_info="|".join(r['result'][1])
        try:
            bjsj=datetime.strptime(r['result'][1][2],"%Y-%m-%d %H:%M")
        except:
            bjsj=datetime.strptime(r['result'][1][1],"%Y-%m-%d %H:%M:%S")
    
        oCmd = Dispatch('ADODB.Command')
        oCmd.ActiveConnection = oConn
        oCmd.CommandText = "update yb set xh_info='"+xh_info+"',tdh='"+str(r['result'][1][5])+"' where cdate(rq&' '&sj)<#"+datetime.strftime(bjsj,"%Y-%m-%d %H:%M")+"# and id="+str(ctnno["id"])
        print oCmd.CommandText
        try:
            oCmd.Execute()
        except:
            print "error"
        oCmd=None
# ##########################################    
# 主程序开始
# ##########################################
# 定义查询的日期        
# 定义更新的报关单数量
# 使用cookie
for x in getexistctnno():
    print "process...",x
    searchctnno(x)

oConn.Close()
oConn=None
